﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace DA
{
    public class DBRoomsType : IDisposable
    {
        string sql;
        SqlHelpers sqlh;
        SqlDataReader dr;

        public DBRoomsType()
        {
            sqlh = new SqlHelpers();
        }
        public DBRoomsType(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }
        /// <summary>
        ///DB   查询所有餐台类型的业务
        /// </summary>
        /// <returns></returns>
        public List<CRoomType> DBRoomsTypeQuery()
        {
            List<CRoomType> lrt = new List<CRoomType>();
            sql = "select * from Roomtype";
            dr = sqlh.RQuery(sql, null, CommandType.Text);
            while (dr.Read())
            {
                CRoomType rt = new CRoomType();
                rt.CrtId1 = Convert.ToInt32(dr["rtId"]);
                rt.CrtName1 = dr["rtName"].ToString();
                rt.CrtLeastCost1 = Convert.ToSingle(dr["rtLeastCost"]);
                rt.CrtMostNumber1 = Convert.ToInt32(dr["rtMostNumber"]);
                rt.CrtAmount1 = Convert.ToInt32(dr["rtAmount"]);
                lrt.Add(rt);
            }
            dr.Close();
            return lrt;
        }

        /// <summary>
        /// 根据餐桌类型名称获取详情
        /// </summary>
        /// <param name="rtName"></param>
        /// <returns></returns>
        public List<CRoomType> DBRoomsTypeQueryByName(string rtName)
        {
            List<CRoomType> lrt = new List<CRoomType>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 20));
            pars[0].Value = rtName;
            sql = "select * from RoomType where rtName=@rtName";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CRoomType rt = new CRoomType();
                rt.CrtId1 = Convert.ToInt32(dr["rtId"]);
                rt.CrtName1 = dr["rtName"].ToString();
                rt.CrtLeastCost1 = Convert.ToSingle(dr["rtLeastCost"]);
                rt.CrtMostNumber1 = Convert.ToInt32(dr["rtMostNumber"]);
                rt.CrtAmount1 = Convert.ToInt32(dr["rtAmount"]);
                lrt.Add(rt);
            }
            dr.Close();
            return lrt;
        }

        /// <summary>
        /// 根据餐台类型查询餐台的方法
        /// </summary>
        /// <param name="rtName"></param>
        /// <returns></returns>
        public int DBRoomsTypeQueryRooms(string rtName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 20));
            pars[0].Value = rtName;
            sql = "select rtId from RoomType where rtName=@rtName";
            return sqlh.ExcuteInsert(sql, pars);
        }

        /// <summary>
        /// 插入餐台类型的方法
        /// </summary>
        /// <param name="tt"></param>
        public void DBRoomsTypInsert(CRoomType rt)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 50));
            pars[0].Value = rt.CrtName1;
            pars.Add(new SqlParameter("@rtLeastCost", SqlDbType.Float));
            pars[1].Value = rt.CrtLeastCost1;
            pars.Add(new SqlParameter("@rtMostNumber", SqlDbType.Int));
            pars[2].Value = rt.CrtMostNumber1;
            pars.Add(new SqlParameter("@rtAmount", SqlDbType.Int));
            pars[3].Value = rt.CrtAmount1;
            sql = "insert into RoomType values(@rtName,@rtLeastCost,@rtMostNumber,@rtAmount)";
            sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        /// <summary>
        /// 修改餐台类型的方法
        /// </summary>
        /// <param name="tt"></param>
        public void DBRoomsTypeUPdate(CRoomType rt)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtId", SqlDbType.Int));
            pars[0].Value = rt.CrtId1;
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 50));
            pars[1].Value = rt.CrtName1;
            pars.Add(new SqlParameter("@rtLeastCost", SqlDbType.Float));
            pars[2].Value = rt.CrtLeastCost1;
            pars.Add(new SqlParameter("@rtMostNumber", SqlDbType.Int));
            pars[3].Value = rt.CrtMostNumber1;
            sql = "update RoomType set rtName=@rtName,rtLeastCost=@rtLeastCost,rtMostNumber=@rtMostNumber where rtId=@rtId";
            sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        /// <summary>
        /// 删除餐台类型的方法
        /// </summary>
        /// <param name="ttId"></param>
        public void DBRoomsTypeDelete(int rtId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtId", SqlDbType.Int));
            pars[0].Value = rtId;
            sql = "delete from RoomType where rtId=@rtId";
            sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        /// <summary>
        /// 修改餐台类型的餐台数量的方法
        /// </summary>
        /// <param name="ttId"></param>
        /// <param name="type"></param>
        public void DBRoomsTypeUpdateAmount(int rtId, int type)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtId", SqlDbType.Int));
            pars[0].Value = rtId;
            if (type == 0)
            {
                sql = "update RoomType set rtAmount=rtAmount+1 where rtId=@rtId";
            }
            if (type == 1)
            {
                sql = "update RoomType set rtAmount=rtAmount-1 where rtId=@rtId";
            }
            sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        /// <summary>
        /// 根据餐台类型名称查询餐台编号的方法
        /// </summary>
        /// <param name="ttName"></param>
        /// <returns></returns>
        public int DBRoomsTypeQueryID(string rtName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 20));
            pars[0].Value = rtName;
            sql = "select rtId from RoomType where rtName=@rtName";
            return sqlh.ExcuteInsert(sql, pars);
        }

    }
}
